from util import json, db, route, Page

bp = route(__name__)

@bp.get("/")
async def index(request):
    res = db.query('select * from user limit 0,10')
    return json(res.as_dict())

# 最新
@bp.get("/new/<page:int>")
async def new(request, page=1):
    params = {
        'page': page,
        'limit': 10,
    }
    # 生成页数
    params['page'] = Page(params['page'], params['limit'])
    sql = 'SELECT a.*, b.nickname,b.account,b.avatar FROM tweet AS a INNER JOIN user AS b ON a.uid = b.id ORDER BY update_date asc LIMIT :page,:limit'
    res = db.query(sql, **params)
    return json(res.as_dict())

# 热门
@bp.get("/hot/<page:int>")
async def hot(request, page=1):
    params = {
        'page': page,
        'limit': 10,
    }
    # 生成页数
    params['page'] = Page(params['page'], params['limit'])
    sql = 'SELECT a.*, b.nickname,b.account,b.avatar FROM tweet AS a INNER JOIN user AS b ON a.uid = b.id ORDER BY heart desc LIMIT :page,:limit'
    res = db.query(sql, **params)
    return json(res.as_dict())


# 城市
@bp.get("/city/<page:int>")
async def city(request, page=1):
    params = {
        'page': page,
        'limit': 10,
    }

    shortname = request.args.get('shortname')
    pinyin = request.args.get('pinyin')
    # 生成页数
    params['page'] = Page(params['page'], params['limit'])
    sql = 'SELECT a.*, b.nickname,b.account,b.avatar FROM tweet AS a INNER JOIN user AS b ON a.uid = b.id '
    sql += 'WHERE b.city like "%'+shortname+'%" OR b.nickname like "%'+shortname+'%" OR b.location like "%'+shortname+'%" OR b.location like "%'+pinyin+'%" ORDER BY update_date desc  LIMIT :page,:limit'
    res = db.query(sql, **params)
    return json(res.as_dict())